package cn.sfkj.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;


import java.util.*;
import javax.naming.*;
import javax.sql.DataSource;

import oracle.sql.BLOB;


//import com.sun.xml.internal.ws.wsdl.writer.document.Service;




public class DBAccess
{
	
    private String jndiName;
    DataSource ds;
    Context ctx;
    private static final ThreadLocal<Connection> connectionHolder = new ThreadLocal<Connection>();
    
    
    

    public DBAccess(String jndiName)
    {

    	
    	
    	ds = null;
        ctx = null;
        this.jndiName = null;
        this.jndiName = jndiName;
        try
        {
            ctx = new InitialContext();
            ds = (DataSource)ctx.lookup(jndiName);
            ctx.close();
        }
        catch(NamingException ex)
        {
           ex.printStackTrace();
        }
    }

    
    public Connection getConnection()
    {
        Connection con;
        for(con = null; con == null;)
            try
            {
                con = ds.getConnection();
          
            }
            catch(Exception ex)
            {
            	   ex.printStackTrace();
                return null;
            }

        return con;
    }
    
    
    public void BeginTransaction()
    {
        Connection con;
            try
            {
            	con = ds.getConnection();
                con.setAutoCommit(false);
                connectionHolder.set(con);
                
            }
            catch(SQLException ex)
            {
            	//try{con.rollback();}catch(Exception sa){System.out.println("发生严重错误!事务处理失败!");}
            	ex.printStackTrace();
               
            }
    }
    
    public Connection getTransaction()
    {
    	System.out.println("1========="+connectionHolder.get());
    	return connectionHolder.get();
    }
    
    public void updateDataTransaction(String sql) throws Exception

    {
    	PreparedStatement stmt = null;String res="";Connection con=null;
    	try
    	{
    		con=connectionHolder.get();
    		if(null!=con){
    		stmt = con.prepareStatement(sql);
    		stmt.executeUpdate();
    		stmt.close();
    		System.out.println("2========="+connectionHolder.get());
    		}
    	}
    	catch(Exception ex)
    	{
    			stmt.close();
    			connectionHolder.get().rollback();
    			endTransaction();
    			System.out.println("发生严重错误!事务处理失败!");		
    	}
    	finally 
        {
				if (stmt != null) 
	           	{  
					stmt.close();  
	           	}  
        }
 
   
    }
    public void endTransaction() throws Exception
    {
    	Connection con=connectionHolder.get();
    		try
            {
            	if (con != null && !con.isClosed()) 
            	{  
            		con.commit();
            	
            	}  	
            }
            catch(SQLException ex)
            {
            	try{
            		if(con!=null)
            		{
            			con.rollback();
            			
            		}
            		System.out.println("发生严重错误!事务处理失败!");
            		ex.printStackTrace();
            	}catch(Exception sa)
            	{
            		//System.out.println("事务回滚失败!");
            		ex.printStackTrace();
            	}
          

            }
            finally 
            {
            	if (con != null && !con.isClosed()) 
            	{  
            		con.close();  
            	}  
            	connectionHolder.remove();
            	System.out.println("3========="+connectionHolder.get());
            }
          

    }

    public Vector select(String sql)
        throws Exception
    {
        Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            for(rs = stmt.executeQuery(); rs.next();)
            {
                Vector line = new Vector();
                int i = 0;
                try
                {
                    do
                    {
                        String value = rs.getString(++i);
                        if(value != null)
                            line.addElement(value);
                        else
                            line.addElement(null);
                    } while(true);
                }
                catch(Exception ex)
                {
                    rows.addElement(line);
                }
            }

        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                con.close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        if(rows.size() == 0)
            return null;
        else
            return rows;
    }

    public String selectOne(String sql)
        throws Exception
    {
        Vector v = null;
        try
        {
            v = select(sql, 1, 1);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        if(v == null)
            return null;
        else
            return v.elementAt(0).toString();
    }
    
   

    public void insert(String sql)
        throws Exception
    {
        updateData(sql);
    }

    public void update(String sql)
        throws Exception
    {
        updateData(sql);
    }

    public void delete(String sql)
        throws Exception
    {
        updateData(sql);
    }

    private Vector select(String sql, int lineNum, int rowNum)
        throws Exception
    {
        Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            if(rowNum == 1)
            {
                if(rs.next())
                {
                    for(int i = 0; i < lineNum; i++)
                    {
                        String value = rs.getString(i + 1);
                        if(value != null)
                            rows.addElement(value);
                        else
                            rows.addElement(null);
                    }

                }
            } else
            {
                while(rs.next()) 
                {
                    Vector line = new Vector();
                    for(int i = 0; i < lineNum; i++)
                    {
                        String value = rs.getString(i + 1);
                        if(value != null)
                            line.addElement(value);
                        else
                            line.addElement(null);
                    }

                    rows.addElement(line);
                    if(--rowNum == 0)
                        break;
                }
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                con.close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        if(rows.size() == 0)
            return null;
        else
            return rows;
    }
    
    
    public boolean selectBlob(String sql,String file) throws Exception
    {
    	boolean flag=true;
    	FileOutputStream w =null;
    	InputStream in=null;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	ResultSet rs = null;
    	try
    	{
    		con = getConnection();
    		stmt = con.prepareStatement(sql);
    		rs = stmt.executeQuery();
    		if(rs.next()) {
    			in =  rs.getBinaryStream(1); 
    			w = new FileOutputStream(file); 
    			byte c[] = new byte[1024]; 
    			int r = 0; 
    			while ((r = in.read(c)) != -1){ 
    				w.write(c,0,r); 
    			} 
    		}
			
        }   
    	catch(Exception ex)
    	{
    		flag=false;
    		ex.getMessage();
    	}
    	finally
    	{
    		w.close(); 
    		in.close(); 
    		rs.close();
    		stmt.close();
    		con.close();
    	}
    	return flag;
    }

    
    
    
    public boolean selectBlobTransaction(String sql,String file) throws Exception
    {
    	boolean flag=true;
    	FileOutputStream w =null;
    	InputStream in=null;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	ResultSet rs = null;
    	try
    	{
    		
    		con = connectionHolder.get();
    		stmt = con.prepareStatement(sql);
    		rs = stmt.executeQuery();
    		if(rs.next()) {
    			in =  rs.getBinaryStream(1); 
    			w = new FileOutputStream(file); 
    			byte c[] = new byte[1024]; 
    			int r = 0; 
    			while ((r = in.read(c)) != -1){ 
    				w.write(c,0,r); 
    			}  
    		}
    		rs.close();
    		stmt.close();
        }   
    	catch(Exception ex)
    	{
    		flag=false;
    		connectionHolder.get().rollback();
    		if(rs != null)rs.close();
            if(stmt != null)stmt.close();
			endTransaction();
			System.out.println("发生严重错误!事务处理失败!");		
    	}
    	finally
    	{
    		w.close(); 
    		in.close(); 
    		
    		 try
             {
                 if(rs != null)rs.close();
                 if(stmt != null)stmt.close();
             }
             catch(SQLException ex)
             {
                 ex.printStackTrace();
             }
    	}
    	return flag;
    }
    
    
    
    public boolean excuteBlob(String sql,FileInputStream file) throws Exception
    {
    	boolean v=true;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	try
    	{
    		con = getConnection();
    		stmt = con.prepareStatement(sql);
    		stmt.setBinaryStream(1,file,file.available()); 
    		stmt.execute(); 
        }   
    	catch(Exception ex)
    	{
    		v=false;
    		throw ex;	
    	}
    	finally
    	{
    		try
    		{
    			stmt.close();
    			con.close();
    		}
    		catch(Exception ex)
    		{
    			throw ex;
    		}
    	}
    	return v;
    }
    
    
    public boolean excuteBlobTransaction(String sql,FileInputStream file) throws Exception
    {
    	boolean v=true;
    	Connection con = null;
    	PreparedStatement stmt = null;
    	try
    	{
    		con=connectionHolder.get();
    		if(null!=con){
    			stmt = con.prepareStatement(sql);
    			stmt.setBinaryStream(1,file,file.available()); 
    			stmt.execute(); 
    			stmt.close();
        		System.out.println("2========="+connectionHolder.get());
        		}
        	}
  
    	catch(Exception ex)
    	{
    		v=false;
    		stmt.close();
			connectionHolder.get().rollback();
			endTransaction();
			System.out.println("发生严重错误!事务处理失败!");		
    	}
    	finally
    	{
    		if (stmt != null) 
           	{  
				stmt.close();  
           	}  
    	}
    	return v;
    }
    
    
    public Vector selectOnePage(String sql, int pageNo, int numPerPage)
        throws Exception
    {
        Vector rows;
        rows = new Vector();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            int rowNo = 1;
            int totalPages = 0;
            int totalRecords;
            Vector line;
            for(totalRecords = 0; rs.next(); totalRecords++)
            {
                if(rowNo > (pageNo - 1) * numPerPage && rowNo <= pageNo * numPerPage)
                {
                    line = new Vector();
                    int i = 0;
                    try
                    {
                        do
                        {
                            String value = rs.getString(++i);
                            if(value != null)
                                line.addElement(value);
                            else
                                line.addElement(null);
                        } while(true);
                    }
                    catch(Exception ex)
                    {
                        rows.addElement(line);
                    }
                }
                rowNo++;
            }

            if(totalRecords % numPerPage == 0)
                totalPages = totalRecords / numPerPage;
            else
                totalPages = totalRecords / numPerPage + 1;
            line = new Vector();
            line.addElement(Integer.toString(totalPages));
            line.addElement(Integer.toString(totalRecords));
            rows.insertElementAt(line, 0);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                con.close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        return rows;
    }

    private void updateData(String sql)
        throws Exception
    {
        Connection con = null;
        PreparedStatement stmt = null;
        try
        {
            Log.printEvent("您要更新数据库的SQL语句为\"" + sql + "\"");
            con = getConnection();
            stmt = con.prepareStatement(sql);
            stmt.executeUpdate();
            Log.printEvent("数据库更新成功！");
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                stmt.close();
                con.close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        return;
    }
    
    
    
    
   

    public List ExecQuery(String sql) throws Exception
    {
    	List list;
        list = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Connection con = null;
        try
        {
            con = getConnection();
            stmt = con.prepareStatement(sql);
            rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numCols = rsmd.getColumnCount();
            String tempValue = "";
            HashMap hm;
            for(; rs.next(); list.add(hm))
            {
                hm = new HashMap();
                for(int i = 1; i <= numCols; i++)
                {
                    tempValue = rs.getString(i);
                    if(tempValue == null)
                        tempValue = "";
                    hm.put(rsmd.getColumnLabel(i).toLowerCase(), tempValue);
                }

            }

        }
        catch(Exception e)
        {
            System.out.println(e + "\n\nError sql is:\n" + sql);
        }
        finally
        {
            try
            {
                if(rs != null)
                    rs.close();
                if(stmt != null)
                    stmt.close();
                if(con != null)
                    try
                    {
                        if(!con.isClosed())
                            con.close();
                    }
                    catch(Exception e)
                    {
                        Log.printEvent("链接池关闭失败!");
                    }
            }
            catch(SQLException ex)
            {
                ex.printStackTrace();
            }
        }
        return list;
    }
    
    
    
    
    
    
    
    
    
}

